package com.zzzzzz.content.reply;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.common.GlobalConstants;
import com.zzzzzz.core.dao.BaseDao;

@Repository
public class ReplyDao extends BaseDao {
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	private static final String ADD = 									"insert into topic_reply(text, topic_id, type_id, user_id, add_dt, upd_dt, status) values (?, ?, ?, ?, ?, ?, ?)";
	private static final String EDIT = 									"update topic_reply set text = ?, upd_dt = ? where id = ?";
	private static final String EDIT_STATUS = 							"update topic_reply set status = ?, upd_dt = ? where id = ?";
	
	private static final String FIND_REPLYVIEW_BY_TOPICID =    			"select r.id, r.text, r.user_id, r.add_dt, u.username, u.pic_set as user_pic_set, (case when u.website is not null then u.website else u.id end) as website from topic_reply r inner join user u on r.user_id = u.id where r.topic_id = ? and r.type_id = ? and r.status = ? order by add_dt";
	private static final String FIND_REPLYVIEW_BY_TOPICID_COUNT = 		"select count(1) from topic_reply r inner join user u on r.user_id = u.id where r.topic_id = ? and r.type_id = ? and r.status = ?";
	
	protected class ReplyViewRowMapper implements RowMapper<ReplyView> {
		public ReplyView mapRow(ResultSet rs, int rn) throws SQLException {
			ReplyView replyView = new ReplyView();
			replyView.setId(rs.getLong("id"));
			replyView.setText(rs.getString("text"));
			replyView.setUserId(rs.getLong("user_id"));
			replyView.setAddDt(rs.getTimestamp("add_dt"));
			replyView.setUsername(rs.getString("username"));
			replyView.setUserPicSet(rs.getBoolean("user_pic_set"));
			replyView.setWebsite(rs.getString("website"));
			return replyView;
		}
	}
	
	public void add(Reply reply) {
		jdbcTemplate.update(ADD, reply.getText(), reply.getTopicId(), reply.getTypeId(), reply.getUserId(), reply.getAddDt(), reply.getUpdDt(), reply.getStatus());
		Long id = jdbcTemplate.queryForLong("SELECT @@IDENTITY");
		reply.setId(id);
	}

	public void edit(Reply reply) {
		jdbcTemplate.update(EDIT, reply.getText(), reply.getUpdDt(), reply.getId());
	}
	
	public void editStatus(Long id, Integer status) {
		jdbcTemplate.update(EDIT_STATUS, status, new Date(), id);
	}
	
	public List<ReplyView> findByTopicId(Long topicId, Integer typeId, int start, int offset){
		StringBuilder sql = new StringBuilder(FIND_REPLYVIEW_BY_TOPICID);
		sql.append(limit());
		
		return jdbcTemplate.query(sql.toString(), new ReplyViewRowMapper(), topicId, typeId, GlobalConstants.STATUS.NORMAL, start, offset);
	}
	public long findByTopicIdCount(Long topicId, Integer typeId){
		return jdbcTemplate.queryForLong(FIND_REPLYVIEW_BY_TOPICID_COUNT, topicId, typeId, GlobalConstants.STATUS.NORMAL);
	}
}
